package com.bobo.web.provider.util.commutils;

import com.bobo.web.api.vo.excel.ExcelTemplate;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.List;

/**
 * Created by v on 2017/12/11.
 */
public class ExcelUtils {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * 获取单元格数据内容为字符串类型的数据
     *
     * @param cell Excel单元格
     * @return String 单元格数据内容
     */
    public static String getStringCellValue(XSSFCell cell) {
        if (cell == null) {
            return "";
        }
        String strCell;
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                strCell = String.valueOf(bd);
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (StringUtils.isEmpty(strCell)) {
            return "";
        }
        return strCell;
    }

    public static String getStringHSSFCellValue(HSSFCell cell) {
        if (cell == null) {
            return "";
        }
        String strCell;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                strCell = String.valueOf(bd);
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                strCell = "";
                break;
            default:
                strCell = "";
                break;
        }
        if (StringUtils.isEmpty(strCell)) {
            return "";
        }
        return strCell;
    }

    public static final File writingExcel(ExcelTemplate template, Integer sheetSize) {
        LOGGER.info("|write excel start |^_^|");
        File file = new File(template.getTitle());
        try {
            int sheetNum = 1;//工作薄sheet编号
            int bodyRowCount = 1;//正文内容行号
            int currentRowCount = 1;//当前的行号
            int perPageNum = sheetSize;//每个工作薄显示50000条数据
            XSSFWorkbook workbook = new XSSFWorkbook();//创建excel
            XSSFSheet sheet = workbook.createSheet(template.getSheet() + sheetNum);//创建一个工作薄
            List<String> lines = template.getLine();
            List list = template.getList();
            LOGGER.info("|write excel list|size={} |^_^|", list.size());
            XSSFRow row;//创建一行
            XSSFCellStyle titleCellStyle = createTitleCellStyle(workbook);
            writeTitleContent(sheet, titleCellStyle, lines);//写入标题
            //第二行开始写入数据
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(bodyRowCount);
                Field[] fields = list.get(i).getClass().getDeclaredFields();
                Object classObject = list.get(i);
                for (int j = 0; j < fields.length; j++) {
                    Field field = fields[j];
                    field.setAccessible(true);
                    Object obj = field.get(classObject);
                    if (obj == null) {
                        obj = "";
                    }
                    row.createCell((short) j).setCellValue(obj.toString());
                }
                if (currentRowCount % perPageNum == 0) {//每个工作薄显示50000条数据
                    sheetNum++;//工作薄编号递增1
                    sheet = workbook.createSheet(template.getSheet() + sheetNum);//创建一个新的工作薄
                    bodyRowCount = 0;//正文内容行号置位为0
                    writeTitleContent(sheet, titleCellStyle, lines);//写入标题
                }
                bodyRowCount++;//正文内容行号递增1
                currentRowCount++;//当前行号递增1
            }

            OutputStream os = new FileOutputStream(file);
            workbook.write(os);
        } catch (Exception e) {
            LOGGER.error("|dynamicWriting error = {} |^_^|", e.getMessage());
        }
        return file;
    }

    /**
     * 设置标题单元样式
     *
     * @param workbook
     * @return
     */
    private static XSSFCellStyle createTitleCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 8);
        font.setFontName(XSSFFont.DEFAULT_FONT_NAME);//设置标题字体
        cellStyle.setFont(font);
        cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);//设置列标题样式
        cellStyle.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);// 设置背景色
        cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
        return cellStyle;
    }

    /**
     * 写入标题行
     */
    private static void writeTitleContent(XSSFSheet sheet, XSSFCellStyle cellStyle, List<String> lines) {
        XSSFRow row = null;
        XSSFCell cell = null;
        //标题
        row = sheet.createRow(0);
        for (int i = 0; i < lines.size(); i++) {
            cell = row.createCell((short) i);
            cell.setCellValue(lines.get(i));
            cell.setCellStyle(cellStyle);
        }
    }

}
